# we get the list of the files we downloaded
# we remove the .files, which are temporary files created by Excel when we open a file,
# we also exclude the first and the last file
files = [f for f in os.listdir('data/') if not f.startswith('.')][1:-1]
# we load a file
def load_data(filename):
df = pd.read_excel('data/'+filename, usecols='B:U', skiprows=[0,1,2,3],
names=['Rank', 'Nat. Sail', 'Skipper/crew', 'Hour FR', 'Latitude', 'Longitude',
'Heading ° 30min', 'Speed kts 30min', 'VMG kts 30min', 'Distance nm 30min',
'Heading ° last report', 'Speed kts last report', 'VMG kts last report','Distance nm last report',
'Heading ° 24h', 'Speed kts 24h', 'VMG kts 24h', 'Distance nm 24h', 'DTF', 'DTL'])
# we drop the na values
df = df.dropna()
# we split the string values into separate columns
df['Nationality'] = df['Nat. Sail'].str.split(' ').str[0].str.replace('\n', '')
df['Sail'] = df['Nat. Sail'].str.split(' ').str[1].apply(int)
df = df.drop('Nat. Sail', axis=1)
df['Skipper'] = df['Skipper/crew'].str.split('\n').str[0]
df['Crew'] = df['Skipper/crew'].str.split('\n').str[1].str.title()
df = df.drop('Skipper/crew', axis=1)
# we convert the latitude and longitude into decimal
# Decimal degrees = Degrees + (Minutes/60) + (Seconds/3600), N & E = + / S & W = -
df['Latitude'] = df['Latitude'].apply(lambda s: (1 if s.split('°')[1].split('.')[1][-1] == 'N' else -1)
* (int(s.split('°')[0])
+ int(s.split('°')[1].split('.')[0])/60
+ int(re.sub('[^0-9]', '', s.split('°')[1].split('.')[1]))/3600))
df['Longitude'] = df['Longitude'].apply(lambda s: (1 if s.split('°')[1].split('.')[1][-1] == 'E' else -1)
* (int(s.split('°')[0])
+ int(s.split('°')[1].split('.')[0])/60
+ int(re.sub('[^0-9]', '', s.split('°')[1].split('.')[1]))/3600))
# we convert the time into a proper datetime value
date = re.search('([0-9]{8})', filename).group(0)
df['Hour FR'] = df['Hour FR'].apply(lambda x: datetime.datetime.strptime(date+'_'+x[:5], '%Y%m%d_%H:%M'))
# we set it as the index
df = df.set_index('Hour FR')
# we convert all the numerical values into floats
df['Rank'] = df['Rank'].apply(int)
numerical_cols = ['Heading ° 30min', 'Speed kts 30min', 'VMG kts 30min', 'Distance nm 30min',
'Heading ° last report', 'Speed kts last report', 'VMG kts last report','Distance nm last report',
'Heading ° 24h', 'Speed kts 24h', 'VMG kts 24h', 'Distance nm 24h', 'DTF', 'DTL']
df[numerical_cols] = df[numerical_cols].apply(lambda x: x.str.replace(re.compile('[a-z]+|°'), '')).apply(pd.to_numeric)
# we reorder the columns
df = df[['Rank', 'Nationality', 'Sail', 'Skipper', 'Crew', 'Latitude', 'Longitude', *numerical_cols]]
return df
# For example let's read the first file
df = load_data(files[1])
df.head()
